/**
 * 账单导入路由
 * 处理文件上传、解析、去重、分类、入库
 */

const express = require('express');
const router = express.Router();
const multer = require('multer');
const fs = require('fs');
const path = require('path');
const db = require('../config/database');
const BillParser = require('../services/parser');
const DeduplicationService = require('../services/dedup');
const ClassificationService = require('../services/classifier');
const { authenticateToken } = require('../middleware/auth');

// 确保uploads目录存在
const uploadsDir = path.join(__dirname, '..', 'uploads');
if (!fs.existsSync(uploadsDir)) {
  fs.mkdirSync(uploadsDir, { recursive: true });
}

// 文件上传配置
const storage = multer.diskStorage({
  destination: function (req, file, cb) {
    cb(null, uploadsDir);
  },
  filename: function (req, file, cb) {
    const uniqueSuffix = Date.now() + '-' + Math.round(Math.random() * 1E9);
    cb(null, uniqueSuffix + '-' + file.originalname);
  }
});

const upload = multer({ 
  storage: storage,
  limits: { fileSize: 10 * 1024 * 1024 }, // 10MB
  fileFilter: function (req, file, cb) {
    const allowedTypes = [
      'text/csv',
      'application/vnd.ms-excel',
      'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
      'application/pdf'
    ];
    const allowedExtensions = ['.csv', '.xls', '.xlsx', '.pdf'];
    const ext = path.extname(file.originalname).toLowerCase();
    
    if (allowedTypes.includes(file.mimetype) || allowedExtensions.includes(ext)) {
      cb(null, true);
    } else {
      cb(new Error('只支持 CSV、XLS、XLSX、PDF 格式'));
    }
  }
});

/**
 * 上传并解析账单
 * POST /api/import/upload
 */
router.post('/upload', authenticateToken, upload.single('file'), async (req, res) => {
  try {
    const { platform, book_id } = req.body;
    const file = req.file;
    
    if (!file) {
      return res.status(400).json({ error: '请上传文件' });
    }

    if (!platform) {
      fs.unlinkSync(file.path); // 删除临时文件
      return res.status(400).json({ error: '请选择平台' });
    }

    if (!book_id) {
      fs.unlinkSync(file.path);
      return res.status(400).json({ error: '请选择账本' });
    }

    // 验证账本权限
    const [books] = await db.query(
      'SELECT * FROM account_books WHERE book_id = ? AND owner_user_id = ?',
      [book_id, req.user.user_id]
    );

    if (books.length === 0) {
      fs.unlinkSync(file.path);
      return res.status(403).json({ error: '没有权限访问该账本' });
    }

    // 解析账单文件（自动识别CSV/Excel/PDF格式）
    let transactions;
    try {
      transactions = await BillParser.parseFile(file.path, platform);
    } catch (parseError) {
      fs.unlinkSync(file.path);
      return res.status(400).json({ 
        error: '账单解析失败', 
        message: parseError.message 
      });
    }

    if (!transactions || transactions.length === 0) {
      fs.unlinkSync(file.path);
      return res.status(400).json({ error: '未能解析到有效数据，请检查文件格式' });
    }

    // 统一数据格式
    const normalizedTransactions = BillParser.normalizeTransactions(transactions);

    // 创建导入记录
    const [result] = await db.query(`
      INSERT INTO import_records 
      (user_id, book_id, platform, file_name, total_count, status)
      VALUES (?, ?, ?, ?, ?, 'processing')
    `, [req.user.user_id, book_id, platform, file.originalname, normalizedTransactions.length]);

    const importId = result.insertId;

    // 保存到临时表
    for (const tx of normalizedTransactions) {
      await db.query(`
        INSERT INTO transactions_import 
        (import_record_id, original_data, parsed_data, status)
        VALUES (?, ?, ?, 'pending')
      `, [
        importId, 
        JSON.stringify(tx.raw_data),
        JSON.stringify(tx)
      ]);
    }

    // 删除临时文件
    fs.unlinkSync(file.path);

    res.json({
      message: '文件上传成功',
      import_id: importId,
      total_count: normalizedTransactions.length,
      preview: normalizedTransactions.slice(0, 10) // 预览前10条
    });

  } catch (error) {
    console.error('导入错误:', error);
    
    // 清理临时文件
    if (req.file && fs.existsSync(req.file.path)) {
      fs.unlinkSync(req.file.path);
    }
    
    res.status(500).json({ 
      error: '导入失败', 
      message: error.message 
    });
  }
});

/**
 * 处理导入数据（去重、分类、入库）
 * POST /api/import/process/:import_id
 */
router.post('/process/:import_id', authenticateToken, async (req, res) => {
  try {
    const { import_id } = req.params;
    
    // 获取导入记录
    const [importRecords] = await db.query(
      'SELECT * FROM import_records WHERE id = ? AND user_id = ?',
      [import_id, req.user.user_id]
    );

    if (importRecords.length === 0) {
      return res.status(404).json({ error: '导入记录不存在' });
    }

    const importRecord = importRecords[0];

    // 获取待处理的交易数据
    const [importTransactions] = await db.query(
      'SELECT * FROM transactions_import WHERE import_record_id = ? AND status = "pending"',
      [import_id]
    );

    if (importTransactions.length === 0) {
      return res.status(400).json({ error: '没有待处理的数据' });
    }

    // 获取去重规则
    const [dedupRules] = await db.query(
      'SELECT * FROM dedup_rules WHERE is_active = TRUE ORDER BY priority DESC LIMIT 1'
    );
    const dedupRule = dedupRules[0] || {};

    // 获取分类规则
    const [categoryRules] = await db.query(
      'SELECT * FROM category_rules WHERE is_active = TRUE'
    );

    // 获取现有交易（最近1000条用于去重）
    const [existingTransactions] = await db.query(`
      SELECT * FROM records 
      WHERE book_id = ? AND is_deleted = 0
      ORDER BY date DESC
      LIMIT 1000
    `, [importRecord.book_id]);

    // 处理统计
    let successCount = 0;
    let duplicateCount = 0;
    let errorCount = 0;

    // 处理每条交易
    for (const importTx of importTransactions) {
      try {
        // MySQL的mysql2驱动会自动将JSON字段解析为对象
        // 所以需要检查类型，避免重复解析
        const parsedData = typeof importTx.parsed_data === 'string' 
          ? JSON.parse(importTx.parsed_data)
          : importTx.parsed_data;
        
        // 1. 去重检查
        const duplicates = await DeduplicationService.findDuplicates(
          parsedData,
          existingTransactions,
          dedupRule
        );

        if (duplicates.length > 0) {
          // 标记为重复
          await db.query(`
            UPDATE transactions_import 
            SET is_duplicate = TRUE, 
                duplicate_with = ?, 
                status = 'duplicate'
            WHERE id = ?
          `, [duplicates[0].existing_id, importTx.id]);
          
          duplicateCount++;
          continue;
        }

        // 2. 智能分类
        const classification = await ClassificationService.classifyTransaction(
          parsedData,
          categoryRules
        );

        const category = classification ? classification.category : 
                        (parsedData.type === 'income' ? '其他收入' : '其他支出');
        const confidence = classification ? classification.confidence : 30;

        // 3. 插入到记录表
        const [insertResult] = await db.query(`
          INSERT INTO records 
          (user_id, book_id, type, amount, category, date, remark, created_at)
          VALUES (?, ?, ?, ?, ?, ?, ?, NOW())
        `, [
          req.user.user_id,
          importRecord.book_id,
          parsedData.type,
          parsedData.amount,
          category,
          parsedData.transaction_time,
          `${parsedData.description} [${parsedData.platform}导入]`
        ]);

        // 4. 更新临时表状态
        await db.query(`
          UPDATE transactions_import 
          SET category = ?, 
              confidence = ?, 
              status = 'imported'
          WHERE id = ?
        `, [category, confidence, importTx.id]);

        successCount++;

      } catch (txError) {
        console.error(`处理交易失败 (ID: ${importTx.id}):`, txError.message);
        
        await db.query(`
          UPDATE transactions_import 
          SET status = 'error', 
              error_message = ?
          WHERE id = ?
        `, [txError.message, importTx.id]);
        
        errorCount++;
      }
    }

    // 更新导入记录
    await db.query(`
      UPDATE import_records 
      SET success_count = ?, 
          duplicate_count = ?, 
          error_count = ?, 
          status = 'completed'
      WHERE id = ?
    `, [successCount, duplicateCount, errorCount, import_id]);

    res.json({
      message: '导入完成',
      success_count: successCount,
      duplicate_count: duplicateCount,
      error_count: errorCount,
      total_count: importTransactions.length
    });

  } catch (error) {
    console.error('处理错误:', error);
    res.status(500).json({ 
      error: '处理失败', 
      message: error.message 
    });
  }
});

/**
 * 获取导入历史
 * GET /api/import/history
 */
router.get('/history', authenticateToken, async (req, res) => {
  try {
    const [records] = await db.query(`
      SELECT 
        ir.*,
        ab.book_name
      FROM import_records ir
      JOIN account_books ab ON ir.book_id = ab.book_id
      WHERE ir.user_id = ?
      ORDER BY ir.import_time DESC
      LIMIT 50
    `, [req.user.user_id]);

    res.json({ data: records });

  } catch (error) {
    console.error('查询错误:', error);
    res.status(500).json({ error: '查询失败' });
  }
});

/**
 * 获取导入详情
 * GET /api/import/detail/:import_id
 */
router.get('/detail/:import_id', authenticateToken, async (req, res) => {
  try {
    const { import_id } = req.params;

    // 获取导入记录
    const [importRecords] = await db.query(`
      SELECT 
        ir.*,
        ab.book_name
      FROM import_records ir
      JOIN account_books ab ON ir.book_id = ab.book_id
      WHERE ir.id = ? AND ir.user_id = ?
    `, [import_id, req.user.user_id]);

    if (importRecords.length === 0) {
      return res.status(404).json({ error: '导入记录不存在' });
    }

    // 获取交易详情
    const [transactions] = await db.query(`
      SELECT * FROM transactions_import 
      WHERE import_record_id = ?
      ORDER BY created_at
    `, [import_id]);

    res.json({
      import_record: importRecords[0],
      transactions: transactions
    });

  } catch (error) {
    console.error('查询错误:', error);
    res.status(500).json({ error: '查询失败' });
  }
});

/**
 * 删除导入记录
 * DELETE /api/import/:import_id
 */
router.delete('/:import_id', authenticateToken, async (req, res) => {
  try {
    const { import_id } = req.params;

    // 验证权限
    const [records] = await db.query(
      'SELECT * FROM import_records WHERE id = ? AND user_id = ?',
      [import_id, req.user.user_id]
    );

    if (records.length === 0) {
      return res.status(404).json({ error: '导入记录不存在' });
    }

    // 删除导入记录（级联删除临时表数据）
    await db.query('DELETE FROM import_records WHERE id = ?', [import_id]);

    res.json({ message: '删除成功' });

  } catch (error) {
    console.error('删除错误:', error);
    res.status(500).json({ error: '删除失败' });
  }
});

module.exports = router;

